package com.scs.application.core.utils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

@Slf4j
public class UtilExcelPoi {

	// ------------------------------------------------------------------------
	public static ArrayList<String[]> getListFromXls(File file, int indexSheet) {
		int nRowCount = 0, nColCount = 0;

		String cellValue = "";
		String[] arrRow = null;
		ArrayList<String[]> arrList = new ArrayList<String[]>();

		FileInputStream fis = null;
		HSSFWorkbook workbook = null;
		HSSFSheet sheet = null;

		HSSFRow row = null;
		HSSFCell cell = null;
		// ------------------------------------------------
		try {
			if (!file.exists()) {
				throw new FileNotFoundException();
			}
			fis = new FileInputStream(file);
			workbook = new HSSFWorkbook(fis);
			sheet = workbook.getSheetAt(indexSheet);

			row = sheet.getRow(0);
			if (row == null) {
				throw new Exception("文件首行数据为空.");
			}
			cell = row.getCell(0);
			while (cell != null) {
				nColCount++;
				cell = row.getCell(nColCount);
			}

			// --------------------------------------------
			row = sheet.getRow(0);
			while (row != null) {
				nRowCount++;
				arrRow = new String[nColCount];
				for (int nCol = 0; nCol < nColCount; nCol++) {
					cell = row.getCell(nCol);
					if (cell == null) continue;
					arrRow[nCol] = getCellValue(cell,workbook) +"";
				}
				arrList.add(arrRow);
				row = sheet.getRow(nRowCount);
			}

			// --------------------------------------------
			return arrList;
		} catch (Exception e) {
			e.printStackTrace();
			return new ArrayList<String[]>();
		} finally {
			if (workbook != null) {
				try {
					workbook = null;
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if (fis != null) {
				try {
					fis.close();
					fis = null;
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}

	private static Object getCellValue(Cell cell, Workbook workbook) {
		Object value = "";
		if (cell != null) {
			try {
				// 判断类型
				switch (cell.getCellType()) {//字符串
					case STRING:
						value = cell.getStringCellValue();
						break;
					case NUMERIC:
						if (HSSFDateUtil.isCellDateFormatted(cell)) {// 日期类型
							// 短日期转化为字符串
							Date date = cell.getDateCellValue();
							if (date != null) {
								// 标准0点 1970/01/01 08:00:00
								if (date.getTime() % 86400000 == 16 * 3600 * 1000 && cell.getCellStyle().getDataFormat() == 14) {
									value = new SimpleDateFormat("yyyy-MM-dd").format(date);
								} else {
									value = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
								}
							}
						} else {// 数值
							//System.out.println("Value:"+cell.getNumericCellValue());
							String numberStr = new HSSFDataFormatter().formatCellValue(cell);
							// 货币格式，如：1,200.00
							if (numberStr.contains(",")) {
								numberStr = numberStr.replace(",", "");
							}
							if (numberStr.contains("E")) {    // 科学计算法
								numberStr = new DecimalFormat("0").format(cell.getNumericCellValue());    //4.89481368464913E14还原为长整数
								value = Long.parseLong(numberStr);
							} else {
								if (numberStr.contains(".")) {    // 小数
									value = Double.parseDouble(numberStr);
								} else {    // 转换为整数
									value = Long.parseLong(numberStr);
								}
							}
						}
						break;
					case FORMULA:
						// 导入时如果为公式生成的数据则无值
						break;
					case BLANK://空
						break;
					case BOOLEAN:
						value = (cell.getBooleanCellValue() == true ? "Y" : "N");
						break;
				}
			} catch (Exception e) {
				System.out.println("读取XLS异常，异常单元格：" + cell);
				throw e;
			}
		}

		return value;

	}

	 /**
     * 判断是否是“02-十一月-2006”格式的日期类型
     */
    private static boolean checkDate(String str){
        String[] dataArr =str.split("-");
        try {
            if(dataArr.length == 3){
                int x = Integer.parseInt(dataArr[0]);
                String y =  dataArr[1];
                int z = Integer.parseInt(dataArr[2]);
                if(x>0 && x<32 && z>0 && z< 10000 && y.endsWith("月")){
                    return true;
                }
            }
        } catch (Exception e) {
            return false;
        }
        return false;
    }

    /**获取纯字符串的数据，不使用excel格式进行格式化
	 */
	public static ArrayList<String[]> getListFromXlsOnlyString(File file, int indexSheet) {
		int nRowCount = 0, nColCount = 0;

		String cellValue = "";
		String[] arrRow = null;
		ArrayList<String[]> arrList = new ArrayList<String[]>();

		FileInputStream fis = null;
		HSSFWorkbook workbook = null;
		HSSFSheet sheet = null;

		HSSFRow row = null;
		HSSFCell cell = null;
		// ------------------------------------------------
		try {
			if (!file.exists()) {
				throw new FileNotFoundException();
			}
			fis = new FileInputStream(file);
			workbook = new HSSFWorkbook(fis);
			sheet = workbook.getSheetAt(indexSheet);

			row = sheet.getRow(0);
			if (row == null) {
				throw new Exception("文件首行数据为空.");
			}
			cell = row.getCell(0);
			while (cell != null) {
				nColCount++;
				cell = row.getCell(nColCount);
			}

			// --------------------------------------------
			row = sheet.getRow(0);
			while (row != null) {
				nRowCount++;
				arrRow = new String[nColCount];
				for (int nCol = 0; nCol < nColCount; nCol++) {
					cell = row.getCell(nCol);
					if (cell == null) continue;
					cell.setCellType(CellType.STRING);
					if (cell != null) {
							cellValue = cell.toString();
					}
					else {
						cellValue = "";
					}
					arrRow[nCol] = cellValue;
				}
				arrList.add(arrRow);
				row = sheet.getRow(nRowCount);
			}

			// --------------------------------------------
			return arrList;
		} catch (Exception e) {
			e.printStackTrace();
			return new ArrayList<String[]>();
		} finally {
			if (workbook != null) {
				try {
					workbook = null;
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if (fis != null) {
				try {
					fis.close();
					fis = null;
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}
}
